package com.cjz.dao;

import com.cjz.domain.Course;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.*;
import java.util.*;

public class DButil {
    Connection conn = null;
    Statement stat = null;
    PreparedStatement pstat = null;
    ResultSet rs =null;
    int successful = 0;

    /**
     * 得到连接
     */
    public Connection getConn(){
        try {
            /*
              加载驱动
             */
            Class.forName("com.mysql.cj.jdbc.Driver");
            /*
             * 建立连接
             */
            conn = DriverManager.getConnection("jdbc:mysql://www.xiaotao2333.top:3306/select_course_system2.0?useSSL=false","xiaotao","mojintao233");
        } catch(Exception | IllegalAccessError e){

            e.printStackTrace();
        }
        return conn;
    }

    //释放资源
    public void release() {
        try {
            if (conn != null) {
                conn.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (stat!=null){
                stat.close();
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查询
    public ResultSet getResultSet(String sql,String []args){
        if (conn == null) {
            conn = this.getConn();
        }
        try {
            pstat=conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pstat.setString(i+1,args[i]);//参数从1开始
            }
            rs=pstat.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public ResultSet getResultSet(String sql) {
        if (conn == null) {
            conn = this.getConn();
        }
        try {
            stat= conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            rs=stat.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public int insert(String sql, Course c) {
        if (conn == null) {
            conn = this.getConn();
        }
        try {
            pstat = conn.prepareStatement(sql);
            pstat.setString(1, c.getCourseId());
            pstat.setString(2, c.getCourseName());
            pstat.setString(3, c.getTeacher());
            pstat.setLong(4, c.getPoint());
            pstat.setString(5, c.getTime1());
            pstat.setString(6, c.getTime2());
            pstat.setString(7, c.getLocation());
            pstat.setLong(8, c.getLimited());
            successful = pstat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return successful;
    }

    /*
	通过课程名获取课程id
	 */
    public String getCourseNameByCourseId(String courseId)
    {
        if (conn == null) {
            conn = this.getConn();
        }
        String courseName=null;
        try {
            pstat=conn.prepareStatement("select courseName from course where courseId=?");
            pstat.setString(1, courseId);
            rs=pstat.executeQuery();
            if(rs.next())
            {
                 courseName=rs.getString("courseName");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return  courseName;
    }

    public List<String> getIsSelectedCourse(String userName, String[] courseId)
    {
        //重复选择的课程名List
        List<String> IsSelectedCoursesList=new ArrayList<>();
        //已经选课的id
        List<String> IsSelectedCourses=new ArrayList<>();
        if (conn == null) {
            conn = this.getConn();
        }
        try {
            pstat = conn.prepareStatement("select * from elective where username=?");
            pstat.setString(1, userName);
            rs = pstat.executeQuery();
            while (rs.next())
            {
                String id = rs.getString("courseId");
                IsSelectedCourses.add(id);
            }
            for(String selectedCourse:IsSelectedCourses)
            {
                for (String id : courseId) {
                    if (selectedCourse.equals(id)) {
                        IsSelectedCoursesList.add(getCourseNameByCourseId(selectedCourse));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return IsSelectedCoursesList;
    }

    /*
    *查询学生已选的课的Id
    */
    public List<String> selectedCoursesList(String userName){
        List<String> IdList = new ArrayList<>();
        if (conn == null) {
            conn = this.getConn();
        }
        try {

            pstat = conn.prepareStatement("select courseId from elective where username=? order by courseId");
            pstat.setString(1, userName);
            ResultSet rs = pstat.executeQuery();
            while (rs.next()) {
                IdList.add(rs.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return IdList;
    }
    /*
    *返回已选课程信息
    */
    public Map<String,Object> selectCourseMap(List<String> idList){
        if (conn == null) {
            conn = this.getConn();
        }
        QueryRunner queryRunner = new QueryRunner();
        Map<String,Object> courseMap= new HashMap<>();
        try {

            for (String id : idList) {
                String sqlString = "select * from course where courseId=?";
                pstat = conn.prepareStatement(sqlString);
                pstat.setString(1, id);
                sqlString = (pstat.toString()).substring(pstat.toString().indexOf(':')+1);
                Course Courser = queryRunner.query(conn,sqlString,new BeanHandler<Course>(Course.class));
                courseMap.put(id, Courser);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return courseMap;
    }

    /*
     *学生选课
     */
    public List<String> selectCourse(String userName,String[] courseId)
    {
        if (conn == null) {
            conn = this.getConn();
        }
        List<String> failCoursesList=new ArrayList<>();
        for(String id:courseId) {
            int availableNumber = getRemainCourseByCourseId(id);
            if (availableNumber > 0) {
                try {
                    pstat = conn.prepareStatement("insert into elective(username,courseId) values(?,?)");
                    pstat.setString(1, userName);
                    pstat.setString(2, id);
                    pstat.executeUpdate();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            else
            {
                failCoursesList.add(getCourseNameByCourseId(id));
            }
        }
        return failCoursesList;
    }

    /*
	通过课程id获取该课程剩余可选数剩余
	 */
    public int getRemainCourseByCourseId(String courseId) {
        int maxNumber = 0;
        int availableNumber = 0;
        if (conn == null) {
            conn = this.getConn();
        }
        try {
            pstat = conn.prepareStatement("select limited from course where courseId=?");
            pstat.setString(1, courseId);
            rs = pstat.executeQuery();
            if (rs.next()) {
                maxNumber = rs.getInt("limited");
            }
            pstat = conn.prepareStatement("select Count(*) from elective where courseId=?");
            pstat.setString(1, courseId);
            rs = pstat.executeQuery();
            if (rs.next()) {
                availableNumber = maxNumber - rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return availableNumber;
    }



}

